const connection = require('../app/database')
class MomentService {
  async create(content, userId) {
    const statement = 'INSERT INTO `moment`(content,user_id) VALUES(?,?);'
    const result = await connection.execute(statement, [content, userId])
    return result
  }
  async queryList(offset = 0, size = 10) {
    const statement = ` SELECT 
        m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
        JSON_OBJECT('id', u.id, 'name', u.name, 'avatarURL', u.avatar_url, 'createTime', u.createAt, 'updateTime', u.updateAt) user,
        (SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id) commentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount
      FROM moment m
      LEFT JOIN user u ON u.id = m.user_id
      LIMIT ? OFFSET ?;`
    const [values] = await connection.execute(statement, [
      String(size),
      String(offset),
    ])
    return values
  }
  async queryById(id) {
    // console.log(id)
    // const statement = `SELECT 
    //     m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
    //     JSON_OBJECT('id', u.id, 'name', u.name, 'avatarURL', u.avatar_url, 'createTime', u.createAt, 'updateTime', u.updateAt) user,
    //     (
    //       SELECT 
    //         JSON_ARRAYAGG(JSON_OBJECT(
    //           'id', c.id, 'content', c.content, 'commentId', c.comment_id,
    //           'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatarURL', u.avatar_url)
    //         ))
    //       FROM comment c
    //       LEFT JOIN user cu ON c.user_id = cu.id
    //       WHERE c.moment_id = m.id
    //     ) comments,
    //     (
    //       JSON_ARRAYAGG(JSON_OBJECT(
    //         'id', l.id, 'name', l.name
    //       ))
    //     ) labels
    //   FROM moment m
    //   LEFT JOIN user u ON u.id = m.user_id
    //   LEFT JOIN moment_label ml ON ml.moment_id = m.id
    //   LEFT JOIN label l ON ml.label_id = l.id
    //   WHERE m.id = ?
    //   GROUP BY m.id;`

      const statement=`SELECT 
        m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
        JSON_OBJECT('id', u.id, 'name', u.name, 'avatarUrl', u.avatar_url) user,
        IF(COUNT(l.id),JSON_ARRAYAGG(
          JSON_OBJECT('id', l.id, 'name', l.name)
        ),NULL) labels,
        (SELECT IF(COUNT(c.id),JSON_ARRAYAGG(
          JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt,
                      'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatarUrl', cu.avatar_url))
        ),NULL) FROM comment c LEFT JOIN user cu ON c.user_id = cu.id WHERE m.id = c.moment_id) comments,
        (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) 
        FROM file WHERE m.id = file.moment_id) images
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      LEFT JOIN moment_label ml ON m.id = ml.moment_id
      LEFT JOIN label l ON ml.label_id = l.id
      WHERE m.id = ?
      GROUP BY m.id;  `
    const [values] = await connection.execute(statement, [id])
    return values
  }
  async updteById(id, content) {
    const statement = 'UPDATE `moment` SET content=? WHERE id=?;'
    const [values] = await connection.execute(statement, [content, id])
    return values
  }
  async remove(momentId) {
    const statement = 'DELETE FROM moment WHERE id=?;'
    const [values] = await connection.execute(statement, [momentId])
    return values
  }
  async hasLabel(momentId, labelId) {
    // console.log(momentId, labelId)
    const statement = `SELECT * FROM moment_label WHERE moment_id=? AND label_id=?;`
    const [values] = await connection.execute(statement, [momentId, labelId])
    return !!values.length
  }
}

module.exports = new MomentService()
